package com.seafood.repository;

import com.seafood.entity.FileUpload;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 文件上传Repository
 */
@Repository
public interface FileUploadRepository extends JpaRepository<FileUpload, Long> {
    
    /**
     * 根据MD5查找文件（去重）
     */
    Optional<FileUpload> findByFileMd5AndIsDeletedFalse(String fileMd5);
    
    /**
     * 根据业务类型和业务ID查找文件
     */
    List<FileUpload> findByBusinessTypeAndBusinessIdAndIsDeletedFalse(
            FileUpload.BusinessType businessType, Long businessId);
    
    /**
     * 根据上传者查找文件
     */
    Page<FileUpload> findByUploaderIdAndIsDeletedFalse(Long uploaderId, Pageable pageable);
    
    /**
     * 根据文件类型查找文件
     */
    Page<FileUpload> findByFileTypeAndIsDeletedFalse(FileUpload.FileType fileType, Pageable pageable);
    
    /**
     * 查找公开文件
     */
    Page<FileUpload> findByIsPublicTrueAndIsDeletedFalse(Pageable pageable);
    
    /**
     * 根据关键词搜索文件
     */
    @Query("SELECT f FROM FileUpload f WHERE f.isDeleted = false " +
           "AND (f.originalFilename LIKE %:keyword% OR f.description LIKE %:keyword% OR f.tags LIKE %:keyword%)")
    Page<FileUpload> searchByKeyword(@Param("keyword") String keyword, Pageable pageable);
    
    /**
     * 统计各文件类型数量
     */
    @Query("SELECT f.fileType, COUNT(f) FROM FileUpload f WHERE f.isDeleted = false GROUP BY f.fileType")
    List<Object[]> countByFileType();
    
    /**
     * 统计各业务类型文件数量
     */
    @Query("SELECT f.businessType, COUNT(f) FROM FileUpload f WHERE f.isDeleted = false GROUP BY f.businessType")
    List<Object[]> countByBusinessType();
    
    /**
     * 获取总文件大小
     */
    @Query("SELECT SUM(f.fileSize) FROM FileUpload f WHERE f.isDeleted = false")
    Long getTotalFileSize();
    
    /**
     * 获取用户上传的总文件大小
     */
    @Query("SELECT SUM(f.fileSize) FROM FileUpload f WHERE f.uploaderId = :uploaderId AND f.isDeleted = false")
    Long getTotalFileSizeByUploader(@Param("uploaderId") Long uploaderId);
    
    /**
     * 查找热门下载文件
     */
    @Query("SELECT f FROM FileUpload f WHERE f.isDeleted = false AND f.isPublic = true " +
           "ORDER BY f.downloadCount DESC")
    Page<FileUpload> findPopularFiles(Pageable pageable);
    
    /**
     * 查找最近上传的文件
     */
    @Query("SELECT f FROM FileUpload f WHERE f.isDeleted = false " +
           "ORDER BY f.createdAt DESC")
    Page<FileUpload> findRecentFiles(Pageable pageable);
    
    /**
     * 查找指定时间范围内的文件
     */
    @Query("SELECT f FROM FileUpload f WHERE f.isDeleted = false " +
           "AND f.createdAt >= :startTime AND f.createdAt <= :endTime")
    List<FileUpload> findByTimeRange(@Param("startTime") LocalDateTime startTime, 
                                    @Param("endTime") LocalDateTime endTime);
    
    /**
     * 查找大文件（超过指定大小）
     */
    @Query("SELECT f FROM FileUpload f WHERE f.isDeleted = false AND f.fileSize > :sizeThreshold")
    List<FileUpload> findLargeFiles(@Param("sizeThreshold") Long sizeThreshold);
    
    /**
     * 查找孤立文件（没有关联业务的文件）
     */
    @Query("SELECT f FROM FileUpload f WHERE f.isDeleted = false AND f.businessId IS NULL")
    List<FileUpload> findOrphanFiles();
    
    /**
     * 根据存储文件名查找
     */
    Optional<FileUpload> findByStoredFilenameAndIsDeletedFalse(String storedFilename);
}